#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""
-------------------------------------------------------------------------------------------------
Copyright:	    This script or any part of this script is property of Etiger Capital. All rights reserved.
Author:		    Zhang Yong
CreateDate:	    2021-09-23
LastUpdate:	    2023-04-07
Version:	    1.0.3
Description:    This is an internal lib to isolate confidential info from external access.
Revision History:
    20220616, Utilize sqlalchemy to replace pymysql, so that the new features in latest pandas version can be used;
    20230220, Remove logging info that originally for debugging purpose;
    20230407, Change the account used to access RDS;
-------------------------------------------------------------------------------------------------
"""

import logging
import time
import pandas as pd
from sqlalchemy import create_engine


logger = logging.getLogger("dataAccess")
logger.setLevel(logging.INFO)
formatter = logging.Formatter("%(asctime)s - %(levelname)s: %(message)s")
console = logging.StreamHandler()
console.setLevel(logging.INFO)
console.setFormatter(formatter)
logger.addHandler(console)


def getFIDMySQLConnection():
    try:
        engine = create_engine(
            "mysql+pymysql://{0}:{1}@{2}/{3}?charset={4}".format('fid_etdataapi', 'Quant#2021',
                                                                 '192.168.10.200:3306',
                                                                 'etiger_core', 'utf8'))
        
        conn = engine.connect()
    except Exception as e:
        return None
    
    return conn


def executeSqlQueryInMYDB(sqlQuery):
    conn = getFIDMySQLConnection()
    if conn is None:
        retry_limit = 5
        i = 1
        while i <= retry_limit:
            time.sleep(1)
            conn = getFIDMySQLConnection()
            if conn is None:
                i += 1
            else:
                break
            
    df = pd.read_sql(sqlQuery, conn)
    conn.close()
    # logger.info("Sql query execution is DONE --->{0}".format(sqlQuery))
    return df


def executeScalarQueryInMYDB(scalarQuery):
    conn = getFIDMySQLConnection()
    if conn is None:
        retry_limit = 5
        i = 1
        while i <= retry_limit:
            time.sleep(1)
            conn = getFIDMySQLConnection()
            if conn is None:
                i += 1
            else:
                break
                
    conn.execute(scalarQuery)
    conn.close()
    # logger.info("Scalar query execution is DONE --->{0}".format(scalarQuery))

